<?php
// 数据库初始化脚本
$host = 'localhost';
$db_name = 'restaurant_ordering';
$username = 'root';
$password = '';

try {
    // 创建数据库连接（不指定数据库）
    $conn = new PDO("mysql:host=$host;charset=utf8", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // 创建数据库
    $conn->exec("CREATE DATABASE IF NOT EXISTS $db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
    echo "数据库创建成功\n";

    // 切换到新数据库
    $conn->exec("USE $db_name");

    // 创建菜单表
    $conn->exec("
        CREATE TABLE IF NOT EXISTS menu_items (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            name_en VARCHAR(255) NOT NULL,
            description TEXT,
            description_en TEXT,
            price DECIMAL(10,2) NOT NULL,
            category VARCHAR(100) NOT NULL,
            image VARCHAR(500),
            available TINYINT(1) DEFAULT 1,
            spicy_level INT DEFAULT 0,
            tags VARCHAR(500),
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_category (category),
            INDEX idx_available (available)
        ) ENGINE=InnoDB
    ");
    echo "菜单表创建成功\n";

    // 创建订单表
    $conn->exec("
        CREATE TABLE IF NOT EXISTS orders (
            id INT AUTO_INCREMENT PRIMARY KEY,
            order_number VARCHAR(50) UNIQUE NOT NULL,
            table_number INT NOT NULL,
            customer_name VARCHAR(100),
            customer_phone VARCHAR(20),
            items JSON NOT NULL,
            total_amount DECIMAL(10,2) NOT NULL,
            tax_amount DECIMAL(10,2) NOT NULL,
            grand_total DECIMAL(10,2) NOT NULL,
            status ENUM('pending', 'preparing', 'ready', 'completed', 'cancelled') DEFAULT 'pending',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            completed_at TIMESTAMP NULL,
            INDEX idx_order_number (order_number),
            INDEX idx_table_number (table_number),
            INDEX idx_status (status),
            INDEX idx_created_at (created_at)
        ) ENGINE=InnoDB
    ");
    echo "订单表创建成功\n";

    // 插入示例菜单数据
    $sample_menu = [
        // 开胃菜
        [
            'name' => '春卷',
            'name_en' => 'Spring Rolls',
            'description' => '香脆春卷配甜辣酱',
            'description_en' => 'Crispy spring rolls with sweet chili sauce',
            'price' => 6.99,
            'category' => 'appetizers',
            'image' => '/static/food/springrolls.jpg',
            'available' => 1,
            'spicy_level' => 0,
            'tags' => 'vegetarian'
        ],
        [
            'name' => '饺子',
            'name_en' => 'Dumplings',
            'description' => '手工饺子配酱油蘸料',
            'description_en' => 'Handmade dumplings with soy dipping sauce',
            'price' => 8.99,
            'category' => 'appetizers',
            'image' => '/static/food/dumplings.jpg',
            'available' => 1,
            'spicy_level' => 0,
            'tags' => ''
        ],

        // 主菜
        [
            'name' => '宫保鸡丁',
            'name_en' => 'Kung Pao Chicken',
            'description' => '经典川菜，麻辣鲜香',
            'description_en' => 'Classic Sichuan dish with chicken, peanuts, and chili',
            'price' => 15.99,
            'category' => 'main-courses',
            'image' => '/static/food/kungpao.jpg',
            'available' => 1,
            'spicy_level' => 3,
            'tags' => 'spicy'
        ],
        [
            'name' => '糖醋里脊',
            'name_en' => 'Sweet and Sour Pork',
            'description' => '外酥里嫩的糖醋里脊',
            'description_en' => 'Crispy pork in sweet and sour sauce',
            'price' => 14.99,
            'category' => 'main-courses',
            'image' => '/static/food/sweet-sour.jpg',
            'available' => 1,
            'spicy_level' => 0,
            'tags' => ''
        ],

        // 面条
        [
            'name' => '牛肉面',
            'name_en' => 'Beef Noodles',
            'description' => '香浓牛肉汤面',
            'description_en' => 'Beef noodles in rich broth',
            'price' => 12.99,
            'category' => 'noodles',
            'image' => '/static/food/beefnoodles.jpg',
            'available' => 1,
            'spicy_level' => 0,
            'tags' => ''
        ],

        // 米饭
        [
            'name' => '炒饭',
            'name_en' => 'Fried Rice',
            'description' => '鸡蛋炒饭配蔬菜',
            'description_en' => 'Egg fried rice with vegetables',
            'price' => 10.99,
            'category' => 'rice',
            'image' => '/static/food/friedrice.jpg',
            'available' => 1,
            'spicy_level' => 0,
            'tags' => 'vegetarian'
        ],

        // 饮料
        [
            'name' => '绿茶',
            'name_en' => 'Green Tea',
            'description' => '清香绿茶',
            'description_en' => 'Fragrant green tea',
            'price' => 3.99,
            'category' => 'drinks',
            'image' => '/static/drinks/greentea.jpg',
            'available' => 1,
            'spicy_level' => 0,
            'tags' => 'hot,non-alcoholic'
        ]
    ];

    $stmt = $conn->prepare("
        INSERT INTO menu_items (name, name_en, description, description_en, price, category, image, available, spicy_level, tags)
        VALUES (:name, :name_en, :description, :description_en, :price, :category, :image, :available, :spicy_level, :tags)
    ");

    foreach ($sample_menu as $item) {
        $stmt->execute($item);
    }
    echo "示例菜单数据插入成功\n";

    echo "数据库初始化完成！\n";

} catch (PDOException $e) {
    echo "错误: " . $e->getMessage() . "\n";
}
?>